package com.holly.unit.genx.util;

import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.StrUtil;
import com.holly.unit.core.enums.DbTypeEnum;
import com.holly.unit.db.api.exception.DaoException;
import com.holly.unit.db.api.exception.enums.DatabaseExceptionEnum;
import com.holly.unit.db.api.util.DatabaseUtil;
import com.holly.unit.gen.api.config.GenCodeCfg;
import com.holly.unit.gen.api.constants.DbConstants;
import com.holly.unit.gen.api.pojo.ColumnInfo;
import com.holly.unit.gen.api.util.DbType2JavaTypeUtil;
import com.holly.unit.gen.api.util.TableUtil;
import com.holly.unit.genx.sqladapter.table.TableColumnListSql;
import com.holly.unit.genx.sqladapter.table.TableListExtSql;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * 类描述: 数据库操作加强类 继承db操作模块
 *
 * @author fxr
 * @version 1.0.0
 * @date 2022/1/14 19:27
 */
@Slf4j
public class DatabaseExtUtil extends DatabaseUtil {

    /**
     * 判断表在数据库中是否存在
     * @param tableName
     * @return
     */
    public static boolean checkTableExist(String tableName){

        Connection conn = null;
        try {
            Class.forName(GenCodeCfg.getDiverName());
            conn = DriverManager.getConnection(
                    GenCodeCfg.getUrl(), GenCodeCfg.getUsername(), GenCodeCfg.getPassword());

            String sql =getCheckTableExitSql(GenCodeCfg.getUrl(),GenCodeCfg.getDbType(),tableName);

            // 构造查询语句
            PreparedStatement preparedStatement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

            ResultSet resultSet = preparedStatement.executeQuery();
            resultSet.last(); // 把指针指向结果集的最后
            int fieldNum = resultSet.getRow(); // 取得最后一条结果的行号,作为类的属性个数
            if(fieldNum>0) return true;
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return false;
    }

    /***
     * 获取数据库中所有的表
     * @return
     */
    public static List<String> findAllTables() {
        List<String> tables = new ArrayList<>();
        Connection conn = null;
        try {
            Class.forName(GenCodeCfg.getDiverName());
            conn = DriverManager.getConnection(
                    GenCodeCfg.getUrl(), GenCodeCfg.getUsername(), GenCodeCfg.getPassword());

            String sql = getTableListExecuteQuerySQL(GenCodeCfg.getUrl(),GenCodeCfg.getDbName());

            // 构造查询语句
            PreparedStatement preparedStatement = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {

                String tableName = resultSet.getString("tableName");

                tables.add(tableName);
            }
            return tables;
        } catch (Exception ex) {
            log.error("查询所有表错误！", ex);
            throw new DaoException(DatabaseExceptionEnum.TABLE_LIST_ERROR, ex.getMessage());
        } finally {
            IoUtil.close(conn);
        }
    }

    /***
     * 根据表名称获取表中所有的列
     * @param tableName 表名
     * @return
     */
    public static List<ColumnInfo> findAllTableColumns(String tableName) {
        ArrayList<ColumnInfo> fieldList = new ArrayList<>();
        Connection conn = null;
        try {
            Class.forName(GenCodeCfg.getDiverName());
            conn = DriverManager.getConnection(
                    GenCodeCfg.getUrl(), GenCodeCfg.getUsername(), GenCodeCfg.getPassword());

            TableColumnListSql tableColumnListSql = new TableColumnListSql();

            String preparedSql = tableColumnListSql.getSql(GenCodeCfg.getUrl());
            String exeSql = getTableColumnsExecuteQuerySQL(GenCodeCfg.getUrl(),preparedSql,tableName,GenCodeCfg.getDbName());

            PreparedStatement preparedStatement = conn.prepareStatement(exeSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();

            // 把指针指向结果集的最后一个
            resultSet.last();
            //取到最后一条结果的行号，作为属性的个数
            int columnNum = resultSet.getRow();

            int num = columnNum;

            //判断数据表中是否存在字段
            if (num > 0) {
                ColumnInfo columnInfo = new ColumnInfo();
                //是否需要转换
                if (GenCodeCfg.isIsHump()) {
                    columnInfo.setFieldName(StrUtil.toCamelCase(resultSet.getString(1).toLowerCase()));
                } else {
                    columnInfo.setFieldName(resultSet.getString(1).toLowerCase());
                }
                //保存字段名
                columnInfo.setColName(resultSet.getString(1).toUpperCase());
                columnInfo.setFieldType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                columnInfo.setColType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                // 精度小数点
                columnInfo.setPrecision(resultSet.getString(4));
                columnInfo.setScale(resultSet.getString(5));
                columnInfo.setStrMaxLength(resultSet.getString(6));
                columnInfo.setNullAble(TableUtil.getNullAble(resultSet.getString(7)));

                // 加载字段页面样式
                loadPageStyleByColumnType(columnInfo);
                columnInfo.setColComment(StrUtil.isBlank(resultSet.getString(3))?columnInfo.getFieldName():resultSet.getString(3));

                //过滤建表模板中字段
                log.debug("columnt.getFieldName() -------------"+columnInfo.getFieldName());

                //
                //页面生成过滤字段
                String[] pageFilterFields = new String[]{};
                if(GenCodeCfg.getPageFilterFields() != null){
                    pageFilterFields = GenCodeCfg.getPageFilterFields().toLowerCase().split(",");
                }

                if(GenCodeCfg.getDbTableId().equals(columnInfo.getFieldName())
                        ||isInArr(columnInfo.getColName().toLowerCase(),pageFilterFields)){
                } else{
                    fieldList.add(columnInfo);
                }

                while (resultSet.previous()) {
                    ColumnInfo po = new ColumnInfo();

                    //是否需要转换
                    if (GenCodeCfg.isIsHump()) {
                        po.setFieldName(StrUtil.toCamelCase(resultSet.getString(1).toLowerCase()));
                    } else {
                        po.setFieldName(resultSet.getString(1).toLowerCase());
                    }
                    //保存字段名
                    po.setColName(resultSet.getString(1).toUpperCase());
                    log.debug("columnt.getFieldName() -------------"+po.getFieldName());
                    if(GenCodeCfg.getDbTableId().equals(po.getFieldName())
                            ||isInArr(po.getColName().toLowerCase(),pageFilterFields)){
                        continue;
                    }
                    po.setFieldType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                    po.setColType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));
                    log.debug("-----po.setFieldType------------"+po.getFieldType());
                    //精度-小数点
                    po.setPrecision(resultSet.getString(4));
                    po.setScale(resultSet.getString(5));
                    po.setStrMaxLength(resultSet.getString(6));
                    po.setNullAble(TableUtil.getNullAble(resultSet.getString(7)));
                    //加载字段页面样式
                    loadPageStyleByColumnType(po);
                    po.setColComment(StrUtil.isBlank(resultSet.getString(3))?po.getFieldName():resultSet.getString(3));
                    fieldList.add(po);
                }
            }else {
                throw new Exception("该表不存在或者表中没有字段");
            }

            log.debug("读取表成功");
            return fieldList;
        } catch (Exception ex) {
            log.error("查询表的所有字段错误！", ex);
            throw new DaoException(DatabaseExceptionEnum.FIELD_GET_ERROR, ex.getMessage());
        } finally {
            IoUtil.close(conn);
        }
    }

    /**
     * 方法:读取表字段信息
     * @param tableName
     */
    public static List<ColumnInfo> findOriginalTableColumn(String tableName) throws Exception {
        ArrayList<ColumnInfo> fieldList = new ArrayList<>();
        Connection conn = null;
        try {
            Class.forName(GenCodeCfg.getDiverName());
            conn = DriverManager.getConnection(
                    GenCodeCfg.getUrl(), GenCodeCfg.getUsername(), GenCodeCfg.getPassword());

            TableColumnListSql tableColumnListSql = new TableColumnListSql();

            String preparedSql = tableColumnListSql.getSql(GenCodeCfg.getUrl());
            String exeSql = getTableColumnsExecuteQuerySQL(GenCodeCfg.getUrl(),preparedSql,tableName,GenCodeCfg.getDbName());

            PreparedStatement preparedStatement = conn.prepareStatement(exeSql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

            //执行查询
            ResultSet resultSet = preparedStatement.executeQuery();

            // 把指针指向结果集的最后一个
            resultSet.last();
            //取到最后一条结果的行号，作为属性的个数
            int columnNum = resultSet.getRow();

            //页面生成过滤字段
            String[] pageFilterFields = new String[]{};
            if(GenCodeCfg.getPageFilterFields() != null){
                pageFilterFields = GenCodeCfg.getPageFilterFields().toLowerCase().split(",");
            }

            int num = columnNum;

            //判断数据表中是否存在字段
            if (num > 0) {
                ColumnInfo columnInfo = new ColumnInfo();
                //是否需要转换
                if (GenCodeCfg.isHump) {
                    columnInfo.setFieldName(StrUtil.toCamelCase(resultSet.getString(1).toLowerCase()));
                } else {
                    columnInfo.setFieldName(resultSet.getString(1).toLowerCase());
                }
                //保存字段名
                columnInfo.setColName(resultSet.getString(1).toUpperCase());
                // 精度小数点
                columnInfo.setPrecision(TableUtil.getNullStr(resultSet.getString(4)));
                columnInfo.setScale(TableUtil.getNullStr(resultSet.getString(5)));
                columnInfo.setStrMaxLength(TableUtil.getNullStr(resultSet.getString(6)));
                columnInfo.setNullAble(TableUtil.getNullAble(resultSet.getString(7)));
                columnInfo.setFieldType(DbType2JavaTypeUtil.columnType2JavaType(resultSet.getString(2).toLowerCase(),columnInfo.getPrecision(),columnInfo.getScale()));
                columnInfo.setColType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));

                // 加载字段页面样式
                loadPageStyleByColumnType(columnInfo);
                columnInfo.setColComment(StrUtil.isBlank(resultSet.getString(3))?columnInfo.getFieldName():resultSet.getString(3));

                //过滤建表模板中字段
                log.debug("columnt.getFieldName() -------------"+columnInfo.getFieldName());
                fieldList.add(columnInfo);


                while (resultSet.previous()) {
                    ColumnInfo po = new ColumnInfo();

                    //是否需要转换
                    if (GenCodeCfg.isIsHump()) {
                        po.setFieldName(StrUtil.toCamelCase(resultSet.getString(1).toLowerCase()));
                    } else {
                        po.setFieldName(resultSet.getString(1).toLowerCase());
                    }
                    //保存字段名
                    po.setColName(resultSet.getString(1).toUpperCase());
                    if(isInArr(po.getColName().toLowerCase(),pageFilterFields)){
                        continue;
                    }
                    //精度-小数点
                    po.setPrecision(TableUtil.getNullStr(resultSet.getString(4)));
                    po.setScale(TableUtil.getNullStr(resultSet.getString(5)));
                    po.setStrMaxLength(TableUtil.getNullStr(resultSet.getString(6)));
                    po.setNullAble(TableUtil.getNullAble(resultSet.getString(7)));
                    po.setFieldType(DbType2JavaTypeUtil.columnType2JavaType(resultSet.getString(2).toLowerCase(), columnInfo.getPrecision(), columnInfo.getScale()));
                    po.setColType(StrUtil.toCamelCase(resultSet.getString(2).toLowerCase()));

                    log.debug("-----po.setFieldType------------"+po.getFieldType());

                    //加载字段页面样式
                    loadPageStyleByColumnType(po);
                    po.setColComment(StrUtil.isBlank(resultSet.getString(3))?po.getFieldName():resultSet.getString(3));
                    fieldList.add(po);
                }
            }else {
                throw new Exception("该表不存在或者表中没有字段");
            }

            log.debug("读取表成功");
            return fieldList;
        } catch (Exception ex) {
            log.error("查询表的所有字段错误！", ex);
            throw new DaoException(DatabaseExceptionEnum.FIELD_GET_ERROR, ex.getMessage());
        } finally {
            IoUtil.close(conn);
        }
    }

    /**
     * 判断元素是否在数组内
     *
     * @param str
     * @param arr
     * @return
     */
    private static boolean isInArr(String str, String[] arr) {

        if (arr == null || arr.length == 0) {
            return false;
        }
        for (int i = 0; i < arr.length; i++) {
            String s = arr[i];
            if (s.equals(str)) {
                return true;
            }
        }
        return false;
    }

    /**
     * 根据数据库字段的类型，加载页面样式
     * @param columnInfo
     */
    private static void loadPageStyleByColumnType(ColumnInfo columnInfo) {

        String fieldType = columnInfo.getFieldType();
        String scale = columnInfo.getScale();

        columnInfo.setClassType("inputxt");
        //必须校验判断
        if(DbConstants.FIELD_NULL_N.equals(columnInfo.getNullAble())){
            columnInfo.setOptionType("*");
        }
        if("datetime".equals(fieldType)||fieldType.contains("time")){
            columnInfo.setClassType("easyui-datetimebox");
        }else if("date".equals(fieldType)){
            columnInfo.setClassType("easyui-datebox");
        }else if(fieldType.contains("int")){
            columnInfo.setOptionType("n");
        }else if("number".equals(fieldType)){
            if(StrUtil.isNotBlank(scale)&&Integer.parseInt(scale)>0){
                columnInfo.setOptionType("d");
            }
        }else if("float".equals(fieldType)||"double".equals(fieldType)||"decimal".equals(fieldType)){
            columnInfo.setOptionType("d");
        }else if("numeric".equals(fieldType)){
            columnInfo.setOptionType("d");
        }
    }



    /***
     * 根据数据库连接的url和数据库名称 填充占位符的值，处理成可运行的查询数据库下所有表的SQL
     * @param url 数据库连接url
     * @param dbName 数据库名称
     * @return
     */
    private static String getTableListExecuteQuerySQL(String url,String dbName){
        String sql = "";
        TableListExtSql tableListExtSql = new TableListExtSql();
        sql = MessageFormat.format(tableListExtSql.getSql(url),dbName);
        return sql;
    }

    /***
     * 根据数据库连接的url和数据库名称 填充占位符的值，处理成可运行的查询表所有字段信息的SQL
     * @param url 数据库连接url
     * @param tableName 数据表名称
     * @param dbName 数据库名称
     * @return
     */
    private static String getTableColumnsExecuteQuerySQL(String dbType,String url,String tableName,String dbName) {

        String sql = "";

        if (dbType.contains(DbTypeEnum.ORACLE.getUrlWords())) {
            sql = MessageFormat.format(url,TableUtil.appendSingleQuote(tableName.toUpperCase()));
        }
        if (dbType.contains(DbTypeEnum.MS_SQL.getUrlWords())) {
            sql = MessageFormat.format(url,TableUtil.appendSingleQuote(tableName.toUpperCase()));
        }
        if (dbType.contains(DbTypeEnum.PG_SQL.getUrlWords())) {
            sql = MessageFormat.format(url,TableUtil.appendSingleQuote(tableName.toUpperCase()),TableUtil.appendSingleQuote(tableName.toUpperCase()));
        }
        if (dbType.contains(DbTypeEnum.MYSQL.getUrlWords())) {
            sql = MessageFormat.format(url,TableUtil.appendSingleQuote(tableName), TableUtil.appendSingleQuote(dbName));
        }
        return sql;
    }

    /***
     * 根据数据库类型和表名 获取判断表是否存在的SQL
     * @param url 配置url连接
     * @param dbType 数据库类型
     * @param tableName 表名
     * @return
     */
    private static String getCheckTableExitSql(String url,String dbType,String tableName) {
        String sql = "";
        //mysql
        if(dbType.equals(DbTypeEnum.MYSQL.getUrlWords())){
            sql = "select column_name,data_type,column_comment,0,0 from information_schema.columns"+
                    " where table_name = "+TableUtil.appendSingleQuote(tableName)+""
                    +" and table_schema = "+TableUtil.appendSingleQuote(GenCodeCfg.getDbName()); // 表名
        }
        //oracle
        if(dbType.equals(DbTypeEnum.ORACLE.getUrlWords())){
            sql = "select colstable.column_name column_name, colstable.data_type data_type, commentstable.comments column_comment"
                    + " from user_tab_cols colstable "
                    + " inner join user_col_comments commentstable "
                    + " on colstable.column_name = commentstable.column_name "
                    + " where colstable.table_name = commentstable.table_name "
                    + " and colstable.table_name = "+TableUtil.appendSingleQuote(tableName.toUpperCase());
        }
        //postgresql
        if(dbType.equals(DbTypeEnum.PG_SQL.getUrlWords())){
            sql = MessageFormat.format(url,TableUtil.appendSingleQuote(tableName.toUpperCase()),TableUtil.appendSingleQuote(tableName.toUpperCase()));
        }
        //sqlserver
        if(dbType.equals(DbTypeEnum.MS_SQL.getUrlWords())){
            sql = MessageFormat.format(url,TableUtil.appendSingleQuote(tableName));
        }

        return sql;
    }

}
